﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using DevExpress.XtraEditors;
using SQLMgtConsole;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Columns;

namespace SqlScriptRunner
{
    public partial class Form1 : DevExpress.XtraBars.Ribbon.RibbonForm
    {
        public Form1()
        {
            InitializeComponent();



            // Add the keywords to the list.
            memoEdit1.Settings.Keywords.Add("select ");
            memoEdit1.Settings.Keywords.Add("from ");
            memoEdit1.Settings.Keywords.Add("where ");
            memoEdit1.Settings.Keywords.Add("set ");
            memoEdit1.Settings.Keywords.Add("update ");
            memoEdit1.Settings.Keywords.Add("insert ");
            memoEdit1.Settings.Keywords.Add("IF ");
            memoEdit1.Settings.Keywords.Add("ELSE ");
            memoEdit1.Settings.Keywords.Add("order by ");

            //memoEdit1.Settings.Keywords.AddRange(new[] {"absolute","action","add","admin"
            //    ,"after","aggregate","alias","all","allocate","alter","and","any","are"
            //    ,"array","as","asc","assertion","at","authorization","before","begin"
            //    ,"binary","bit","blob","boolean","both","breadth","by","call","cascade"
            //    ,"cascaded","case","cast","catalog","char","character","check","class"
            //    ,"clob","close","collate","collation","column","commit","completion"
            //    ,"connect","connection","constraint","constraints","constructor"
            //    ,"continue","corresponding","create","cross","cube","current"
            //    ,"current_date","current_path","current_role","current_time"
            //    ,"current_timestamp","current_user","cursor","cycle","data","date"
            //    ,"day","deallocate","dec","decimal","declare","default","deferrable"
            //    ,"deferred","delete","depth","deref","desc","describe","descriptor"
            //    ,"destroy","destructor","deterministic","dictionary","diagnostics"
            //    ,"disconnect","distinct","domain","double","drop","dynamic","each"
            //    ,"else","end","end-exec","equals","escape","every","except","exception"
            //    ,"exec","execute","external","false","fetch","first","float","for"
            //    ,"foreign","found","from","free","full","function","general","get"
            //    ,"global","go","goto","grant","group","grouping","having","host"
            //    ,"hour","identity","if","ignore","immediate","in","indicator"
            //    ,"initialize","initially","inner","inout","input","insert","int"
            //    ,"integer","intersect","interval","into","is","isolation","iterate"
            //    ,"join","key","language","large","last","lateral","leading","left"
            //    ,"less","level","like","limit","local","localtime","localtimestamp"
            //    ,"locator","map","match","minute","modifies","modify","module","month"
            //    ,"names","national","natural","nchar","nclob","new","next","no","none"
            //    ,"not","null","numeric","object","of","off","old","on","only","open"
            //    ,"operation","option","or","order","ordinality","out","outer","output"
            //    ,"pad","parameter","parameters","partial","path","postfix","precision"
            //    ,"prefix","preorder","prepare","preserve","primary","prior","privileges"
            //    ,"procedure","public","read","reads","real","recursive","ref","references"
            //    ,"referencing","relative","restrict","result","return","returns","revoke"
            //    ,"right","role","rollback","rollup","routine","row","rows","savepoint"
            //    ,"schema","scroll","scope","search","second","section","select","sequence"
            //    ,"session","session_user","set","sets","size","smallint","some|","space"
            //    ,"specific","specifictype","sql","sqlexception","sqlstate","sqlwarning"
            //    ,"start","state","statement","static","structure","system_user","table"
            //    ,"temporary","terminate","than","then","time","timestamp","timezone_hour"
            //    ,"timezone_minute","to","trailing","transaction","translation","treat"
            //    ,"trigger","true","under","union","unique","unknown","unnest","update"
            //    ,"usage","user","using","value","values","varchar","variable","varying"
            //    ,"view","when","whenever","where","with","without","work","write","year"
            //    ,"zone" }); 
            


            // Set the comment identifier. 
            // For Lua this is two minus-signs after each other (--).
            // For C++ code we would set this property to "//".
            memoEdit1.Settings.Comment = "--";

            // Set the colors that will be used.
            memoEdit1.Settings.KeywordColor = Color.Blue;
            memoEdit1.Settings.CommentColor = Color.Green;
            memoEdit1.Settings.StringColor = Color.Gray;
            memoEdit1.Settings.IntegerColor = Color.Red;

            // Let's not process strings and integers.
            memoEdit1.Settings.EnableStrings = false;
            memoEdit1.Settings.EnableIntegers = false;

            // Let's make the settings we just set valid by compiling
            // the keywords to a regular expression.
            memoEdit1.CompileKeywords();

            // Load a file and update the syntax highlighting.
            //memoEdit1.LoadFile("script.txt", RichTextBoxStreamType.PlainText);
            memoEdit1.ProcessAllLines();


            
         
        }

        #region Properties
        public List<Tablo> TabloListesi { get; set; }
        public List<Tablo> ViewListesi { get; set; }
        #endregion

        #region Fields
        private Control _cntNormal;

        private const string SP_GETDEPEND = "uspGetSysObjectsDependencies";

        #endregion 

        #region Methots
        private void OpenFile()
        {
            try
            {
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {


                    var ms = openFileDialog1.OpenFile();

                    StreamReader sr = new StreamReader(ms);

                    string sorgu = sr.ReadToEnd();

                    memoEdit1.Text = sorgu;

                    //memoEdit1.ProcessAllLines(); 
                    return;

                    sr.Close();
                    sr.Dispose();

                    int sira = 1;
                    var dizi = Regex.Split(sorgu, string.Format("{0}GO{0}", Environment.NewLine));

                    listBoxControl1.Items.Clear();

                    listBoxControl1.Items.AddRange(
                        (from i in dizi
                         select new Sorgu() { Cumle = i, Sira = sira++ }).ToArray());
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
                
        }

        private void GetView()
        {
            List<Tablo> liste = new List<Tablo>();
            using (SqlConnection con = new SqlConnection(bEditConStr.EditValue.ToString()))
            {

                string sorgu = "SELECT NAME FROM sys.views ORDER BY NAME";
                SqlCommand cmd = new SqlCommand(sorgu, con);
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    liste.Add(new Tablo() 
                    {
                        Adi = dr["NAME"].ToString() //,
                       // Count = GetCount(dr["NAME"].ToString())
                    });
                }
            }

            ViewListesi = liste;

            listBoxControl2.ValueMember = "Adi";
            listBoxControl2.DisplayMember = "GorunenAd";
            listBoxControl2.DataSource = liste;

        }

        private void GetTableList()
        {
            List<Tablo> liste = new List<Tablo>();
            using (SqlConnection con = new SqlConnection(bEditConStr.EditValue.ToString()))
            {
                string sorgu = "SELECT NAME FROM SYS.TABLES ORDER BY NAME";
                SqlCommand cmd = new SqlCommand(sorgu, con);
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    liste.Add(new Tablo() 
                    { 
                        Adi = dr["NAME"].ToString()//, 
                        //Count = GetCount(dr["NAME"].ToString())
                    });
                }
            }
            TabloListesi = liste;

            listBoxControl1.ValueMember = "Adi";
            listBoxControl1.DisplayMember = "GorunenAd";
            listBoxControl1.DataSource = liste;
        }

        private int GetCount(string p) 
        {
            SqlConnection con1 = new SqlConnection(bEditConStr.EditValue.ToString()); 
            
            
            string sorgu = "SELECT COUNT(*) as SAYI FROM " + p;            
            SqlCommand cmd = new SqlCommand(sorgu,con1);
            con1.Open();
            int i = 0; 

            var read  = cmd.ExecuteReader();
            
            while (read.Read())
            {
                i = int.Parse(read["SAYI"].ToString());
            }

            read.Close();
            con1.Close(); 

            return i; 
        }

        private void OpenConnect()
        {
            if (bEditConStr.Enabled)
            {
                try
                {
                    SqlConnection sCon = new SqlConnection();
                    sCon.ConnectionString = bEditConStr.EditValue.ToString();
                    sCon.Open();
                    sCon.Close();
                    bEditConStr.Enabled = false;
                    bBtnExecuteNonQuery.Enabled = true;
                    bBtnExecuteTable.Enabled = true; 
                    bbBaglan.Caption = "Bağlantıyı Kes";
//                    toolStrip2.Visible = true;
                    bbBaglan.LargeGlyph = Resource1.disconnect; 
                    GetTableList();
                    GetView();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                bEditConStr.Enabled = true;
                bBtnExecuteNonQuery.Enabled = false;
                bBtnExecuteTable.Enabled = false; 
                bbBaglan.Caption = "Bağlan";
               // toolStrip2.Visible = false;
                bbBaglan.LargeGlyph = Resource1.connect; 

            }
        }

        private void ExecuteDataTable()
        {
            try
            {

                string sorgu = memoEdit1.SelectedText;
                if (sorgu.Length == 0)
                    sorgu = memoEdit1.Text;
                
                SqlConnection con = new SqlConnection(bEditConStr.EditValue.ToString());
                SqlCommand cmd = new SqlCommand(sorgu, con);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                int sonuc = da.Fill(table);
                gridView1.Columns.Clear();
                gridControl1.DataSource = table;
                
                bLbl.Caption = string.Format("{0} Sonuç", sonuc);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ExecudeNonQuery()
        {
            SqlConnection con = new SqlConnection(bEditConStr.EditValue.ToString());

            SqlCommand cmd = new SqlCommand(memoEdit1.Text, con);
            con.Open(); 
            int i =  cmd.ExecuteNonQuery();
            con.Close(); 
            List<int> liste = new List<int>();

            liste.Add(i);
            gridView1.Columns.Clear(); 
            gridControl1.DataSource = liste; 


        }


        //private void TamEkranYap(GridControl gc)
        //{
        //    Form frm = gc.FindForm();
        //    if (frm != null)
        //    {
        //        foreach (Control c in frm.Controls)
        //        {
        //            if (c.Visible)
        //            {
        //                c.Tag = "[FullScreen]";
        //                c.Visible = false;
        //            }
        //        }
        //        _cntNormal = gc.Parent;
        //        //frm.Controls.Add(gc);
        //        PanelControl panelControl = new PanelControl();
        //        SimpleButton sbtn = new SimpleButton();
        //        panelControl.Controls.Add(sbtn);
        //        sbtn.Dock = DockStyle.Top;
        //        sbtn.Image = MyImageList.Images[MyImageIndexes[NavImages.TamEkran]];
        //        sbtn.Text = "Tam Ekranı Kapat";
        //        sbtn.Click += new EventHandler(sbtn_Click);
        //        panelControl.Controls.Add(gc);
        //        panelControl.Dock = DockStyle.Fill;
        //        gc.BringToFront();
        //        gc.Visible = true;
        //        gc.Tag = gc.Dock;

        //        gc.Dock = DockStyle.Fill;
        //        frm.Controls.Add(panelControl);
        //    }

        //}

        //private void EskiHaleGetir(GridControl gc)
        //{
        //    Form frm = gc.FindForm();
        //    gc.Dock = (DockStyle)gc.Tag;
        //    Control gz = gc.Parent;
        //    _cntNormal.Controls.Add(gc);
        //    gz.Dispose();
        //    foreach (Control c in frm.Controls)
        //    {
        //        if (!c.Visible && c.Tag != null && c.Tag.ToString() == "[FullScreen]")
        //        {
        //            c.Visible = true;
        //        }
        //    }
        //}

        #endregion 
        
        #region Events


        private void bbOpen_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            OpenFile();
        }

        private void bBtnExecute_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            ExecuteDataTable();
        }

        private void bbBaglan_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            OpenConnect();
        }

        private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            ExecudeNonQuery();
        }

        private void bBtnGeriAl_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            memoEdit1.Undo();

        }

        private void barButtonItem2_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            memoEdit1.SelectAll();
        }

        private void barButtonItem1_ItemClick_1(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            memoEdit1.Paste();
        }

        private void bBtnKopyala_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            memoEdit1.Copy();
        }

        private void bBtnKes_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            memoEdit1.Cut();
        }

        private void depenctToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (true)
            {
                // ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/tsqlref9/html/44fdc387-67b0-4139-8bf5-ed26cf640cd1.htm
                // Data-type for column "name" in table "sys.sysobjects" is "sysname"
                // ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2000.en/tsqlref/ts_da-db_7msw.htm
                // sysname is a system-supplied user-defined data type 
                // that is functionally equivalent to nvarchar(128) 
                // and is used to reference database object names.

                string strQuery = "";
                if (true)//htSProc != null && htSProc.Count > 0 && htSProc.ContainsKey(SP_GETDEPEND))
                {
                    string strPath = @"Query\" + SP_GETDEPEND + ".sql";

                    if (File.Exists(strPath))
                    {
                        // Create a file to write to.
                        strQuery = File.ReadAllText(strPath);
                        // No need qualified name
                        string strTableName = listBoxControl1.SelectedValue.ToString();
                        strQuery = strQuery.Replace("@SysObject", "'" + strTableName + "'");
                        Exception exDisplay = new Exception();
                        SqlConnection con = new SqlConnection(bEditConStr.EditValue.ToString());
                        SqlCommand cmd = new SqlCommand(strQuery, con);
                        SqlDataAdapter da = new SqlDataAdapter(cmd);

                        DataTable dtDepend = new DataTable(); ;//= dbfac.fnExecDBCommonQuery(m_strDBProvider,
                        da.Fill(dtDepend);
                        //                          fnCreateConnection(m_CurrSelectNode.Parent.Parent.Text),
                        //                        strQuery, ref exDisplay);
                        if (dtDepend != null)
                        {
                            frmDependency frmDepend = new frmDependency(strTableName, dtDepend);
                            frmDepend.ShowDialog();
                            frmDepend.Dispose();
                        }
                    }
                    else
                    {
                        MessageBox.Show(SP_GETDEPEND + ".sql file not found");
                    }
                }
            }
        }

        private void tsMenuColumn_Click(object sender, EventArgs e)
        {
            string sorgu = string.Format(@"select 
                TCOL.COLUMN_NAME, TCOL.DATA_TYPE, TCOL.IS_NULLABLE,RCON.UNIQUE_CONSTRAINT_NAME
                from INFORMATION_SCHEMA.COLUMNS AS TCOL
                LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS TCON ON
                TCOL.TABLE_NAME = TCON.TABLE_NAME AND
                TCOL.COLUMN_NAME = TCON.COLUMN_NAME
                LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RCON ON
                TCON.CONSTRAINT_NAME = RCON.CONSTRAINT_NAME
                where TCOL.TABLE_NAME  = '{0}'"
                , ((DevExpress.XtraEditors.ListBoxControl)contextMenuStrip1.SourceControl).Text);

            memoEdit1.Text = sorgu;
            bBtnExecuteTable.PerformClick();
        }



        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            if (TabloListesi != null)
                listBoxControl1.DataSource = TabloListesi.Where(vi => vi.Adi.ToUpper().Contains(tEditFilitre.Text.ToUpper())).ToList();
            if (ViewListesi != null)
                listBoxControl2.DataSource = ViewListesi.Where(vi => vi.Adi.ToUpper().Contains(tEditFilitre.Text.ToUpper())).ToList();
        }

        private void listBoxControl1_DoubleClick(object sender, EventArgs e)
        {
            var boxlist = sender as ListBoxControl;
            Clipboard.SetText(boxlist.SelectedValue.ToString());

            memoEdit1.Text += Clipboard.GetText(); 
        }

        private void selectToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                memoEdit1.Text = "SELECT * FROM " + ((DevExpress.XtraEditors.ListBoxControl)contextMenuStrip1.SourceControl).Text;
                bBtnExecuteTable.PerformClick();
            }
            catch
            { }
        }
        #endregion

        private void barButtonItem3_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            frmServerConfig frm = new frmServerConfig();
            if (frm.ShowDialog() == DialogResult.OK)
            {
                bEditConStr.EditValue = frm.ConnectionString; 
            }
                
        }

        private void barButtonItem4_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            gridView1.BestFitColumns(); 
        }

        private void gridView1_ShowGridMenu(object sender, DevExpress.XtraGrid.Views.Grid.GridMenuEventArgs e)
        {
            if (e.HitInfo.Column.ColumnType.Name == "Int32")
            {
                var menuItem = new DevExpress.Utils.Menu.DXMenuItem("Add LookUp");
                menuItem.Click += new EventHandler(menuItem_Click);
                e.Menu.Items.Add(menuItem);

                CurrentColumn = e.HitInfo.Column;
            }
        }

        GridColumn CurrentColumn; 

        void menuItem_Click(object sender, EventArgs e)
        {
            frmGetLookUp frm = new frmGetLookUp();
            frm.TableDataSource = TabloListesi;
            frm.ConStr = bEditConStr.EditValue.ToString();
            if (frm.ShowDialog() == DialogResult.OK)
            {

                if (frm.CurrentLookUp != null)
                    CurrentColumn.ColumnEdit = frm.CurrentLookUp;
            }
        }
    

    }

    class Sorgu
    {
        public override string ToString()
        {
            return Sira.ToString() + " - " + Cumle.Length.ToString();
        }
        public int Sira { get; set; }
        public string Cumle { get; set; }
    }

    public class Tablo
    {
        public string Adi { get; set; }

        public int Count { get; set; }

        public string GorunenAd
        {
            get
            {
                return string.Format("{0}",Adi);// -- ({1})", Adi, Count); 

            }
        }        
        
    }

    public static class MyEx
    {
        public static int MyExt<T>
            
            (this IEnumerable<T> th)
        {
            return 1;
        }
    }

    public class MyReposiyory 
    {       

    }

}
